package com.shlh.saas.mapper.linkedin;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.shlh.saas.entity.linkedin.LinkedInUser;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;

/**
 * LinkedIn用户数据访问接口
 */
@Mapper
public interface LinkedInUserMapper extends BaseMapper<LinkedInUser> {

    /**
     * 根据关键词搜索LinkedIn数据
     * 
     * @param keyword 关键词
     * @return 搜索结果列表
     */
    @Select("SELECT * FROM ly_user WHERE " +
            "full_name LIKE CONCAT('%', #{keyword}, '%') OR " +
            "company_name LIKE CONCAT('%', #{keyword}, '%') OR " +
            "job_title LIKE CONCAT('%', #{keyword}, '%') OR " +
            "emails LIKE CONCAT('%', #{keyword}, '%') OR " +
            "industry LIKE CONCAT('%', #{keyword}, '%')")
    List<LinkedInUser> searchByKeyword(@Param("keyword") String keyword);

    /**
     * 在所有字段中搜索关键词（带分页）
     */
    @Select("SELECT * FROM ly_user WHERE " +
            "full_name LIKE CONCAT('%', #{keyword}, '%') OR " +
            "company_name LIKE CONCAT('%', #{keyword}, '%') OR " +
            "job_title LIKE CONCAT('%', #{keyword}, '%') OR " +
            "industry LIKE CONCAT('%', #{keyword}, '%') OR " +
            "location LIKE CONCAT('%', #{keyword}, '%') OR " +
            "emails LIKE CONCAT('%', #{keyword}, '%') OR " +
            "phone_numbers LIKE CONCAT('%', #{keyword}, '%') OR " +
            "mobile LIKE CONCAT('%', #{keyword}, '%') OR " +
            "skills LIKE CONCAT('%', #{keyword}, '%') OR " +
            "linkedin_url LIKE CONCAT('%', #{keyword}, '%') " +
            "LIMIT #{offset}, #{limit}")
    List<LinkedInUser> searchInAllFieldsWithPagination(
        @Param("keyword") String keyword, 
        @Param("offset") int offset, 
        @Param("limit") int limit
    );
    
    /**
     * 统计在所有字段中包含关键词的记录总数
     */
    @Select("SELECT COUNT(*) FROM ly_user WHERE " +
            "full_name LIKE CONCAT('%', #{keyword}, '%') OR " +
            "company_name LIKE CONCAT('%', #{keyword}, '%') OR " +
            "job_title LIKE CONCAT('%', #{keyword}, '%') OR " +
            "industry LIKE CONCAT('%', #{keyword}, '%') OR " +
            "location LIKE CONCAT('%', #{keyword}, '%') OR " +
            "emails LIKE CONCAT('%', #{keyword}, '%') OR " +
            "phone_numbers LIKE CONCAT('%', #{keyword}, '%') OR " +
            "mobile LIKE CONCAT('%', #{keyword}, '%') OR " +
            "skills LIKE CONCAT('%', #{keyword}, '%') OR " +
            "linkedin_url LIKE CONCAT('%', #{keyword}, '%')")
    long countInAllFields(@Param("keyword") String keyword);
    
    /**
     * 查询所有数据（带分页）
     */
    @Select("SELECT * FROM ly_user LIMIT #{offset}, #{limit}")
    List<LinkedInUser> selectAllWithPagination(
        @Param("offset") int offset, 
        @Param("limit") int limit
    );
    
    /**
     * 统计总记录数
     */
    @Select("SELECT COUNT(*) FROM ly_user")
    long countAll();
    
    /**
     * 查找相似的姓名
     */
    @Select("SELECT DISTINCT full_name FROM ly_user WHERE full_name LIKE CONCAT('%', #{keyword}, '%') LIMIT 5")
    List<String> findSimilarFullNames(@Param("keyword") String keyword);
    
    /**
     * 查找相似的公司
     */
    @Select("SELECT DISTINCT company_name FROM ly_user WHERE company_name LIKE CONCAT('%', #{keyword}, '%') LIMIT 5")
    List<String> findSimilarCompanies(@Param("keyword") String keyword);
    
    /**
     * 查找相似的行业
     */
    @Select("SELECT DISTINCT industry FROM ly_user WHERE industry LIKE CONCAT('%', #{keyword}, '%') LIMIT 5")
    List<String> findSimilarIndustries(@Param("keyword") String keyword);

    /**
     * 通过关键词搜索具有电子邮箱的LinkedIn用户
     * 
     * @param keyword 搜索关键词
     * @param offset 偏移量
     * @param limit 限制数量
     * @return 符合条件的LinkedIn用户列表
     */
    @Select("<script>" +
            "SELECT * FROM ly_user WHERE emails IS NOT NULL AND emails != '' " +
            "AND (full_name LIKE CONCAT('%',#{keyword},'%') " +
            "OR company_name LIKE CONCAT('%',#{keyword},'%') " +
            "OR company_website LIKE CONCAT('%',#{keyword},'%')) " +
            "ORDER BY id " +
            "LIMIT #{limit} OFFSET #{offset}" +
            "</script>")
    List<LinkedInUser> searchLinkedInUsersWithEmail(@Param("keyword") String keyword, 
                                                   @Param("offset") int offset, 
                                                   @Param("limit") int limit);
    
    /**
     * 统计通过关键词搜索具有电子邮箱的LinkedIn用户数量
     * 
     * @param keyword 搜索关键词
     * @return 符合条件的用户数量
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM ly_user WHERE emails IS NOT NULL AND emails != '' " +
            "AND (full_name LIKE CONCAT('%',#{keyword},'%') " +
            "OR company_name LIKE CONCAT('%',#{keyword},'%') " +
            "OR company_website LIKE CONCAT('%',#{keyword},'%'))" +
            "</script>")
    long countLinkedInUsersWithEmail(@Param("keyword") String keyword);
    
    /**
     * 通过关键词搜索具有电话的LinkedIn用户
     * 
     * @param keyword 搜索关键词
     * @param offset 偏移量
     * @param limit 限制数量
     * @return 符合条件的LinkedIn用户列表
     */
    @Select("<script>" +
            "SELECT * FROM ly_user WHERE phone_numbers IS NOT NULL AND phone_numbers != '' " +
            "AND (full_name LIKE CONCAT('%',#{keyword},'%') " +
            "OR company_name LIKE CONCAT('%',#{keyword},'%') " +
            "OR company_website LIKE CONCAT('%',#{keyword},'%')) " +
            "ORDER BY id " +
            "LIMIT #{limit} OFFSET #{offset}" +
            "</script>")
    List<LinkedInUser> searchLinkedInUsersWithPhone(@Param("keyword") String keyword, 
                                                   @Param("offset") int offset, 
                                                   @Param("limit") int limit);
    
    /**
     * 统计通过关键词搜索具有电话的LinkedIn用户数量
     * 
     * @param keyword 搜索关键词
     * @return 符合条件的用户数量
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM ly_user WHERE phone_numbers IS NOT NULL AND phone_numbers != '' " +
            "AND (full_name LIKE CONCAT('%',#{keyword},'%') " +
            "OR company_name LIKE CONCAT('%',#{keyword},'%') " +
            "OR company_website LIKE CONCAT('%',#{keyword},'%'))" +
            "</script>")
    long countLinkedInUsersWithPhone(@Param("keyword") String keyword);

    /**
     * 根据邮箱精确查询LinkedIn用户
     * 
     * @param email 邮箱地址
     * @return 匹配的LinkedIn用户列表
     */
    @Select("SELECT * FROM ly_user WHERE emails LIKE CONCAT('%', #{email}, '%') LIMIT 10")
    List<LinkedInUser> findByEmail(@Param("email") String email);

    /**
     * 根据id精确查询LinkedIn用户
     * @param id
     * @return
     */
    @Select("SELECT * FROM ly_user WHERE id = #{id} LIMIT 1")
    LinkedInUser selectByLinkedInId(Long id);
}